import sqlite3
import csv
import sys
import numpy as np
import matplotlib.pyplot as plt



def csvsql(csvFile, bd):
	
# connetion sqlite of the base dados
	
	connection = sqlite3.connect(bd)
	
# connection cursor of the bd
	
	c = connection.cursor()
	
#Create table in not exists of the parametre

	c.execute('''create table if not exists dados(codIntituicao varchar(100), codCurso varchar(100),\
				nomeInstituicao varchar(100), nomeCurso varchar(75), grau varchar(22), vagasIniciais varchar(100), \
				colocados varchar(100), notaUltCand varchar(100), vagasSobrantes varchar(100))''')
				
# save the results nd conection	
			
	connection.commit()
	
#Open csvFile and read of the csvFile and delimiter of the column ','

	spamReader = csv.reader(open(csvFile, "r"), delimiter=',')

# Read row of csvFile
	
	for row in spamReader:
		
#Read row of the column 0 of the column 9
# replace de "," par '.' and "\\" '\\\\' (informacao recolhida trabalho 2011/2012)	
	
		for i in range(0,9):
			
			row[i] = row[i].replace(",", r'.');
			
			row[i] = row[i].replace("\\", r'\\\\');
			
#Insert of the dados of the tabela
		
		comando = '''insert or replace into dados (codIntituicao, codCurso,\
				     nomeInstituicao, nomeCurso, grau, vagasIniciais,\
				     colocados, notaUltCand,	vagasSobrantes) values ("{0}","{1}","{2}","{3}","{4}","{5}",\
										                                "{6}","{7}","{8}")'''.format(row[0], row[1], row[2],
										                                 row[3], row[4], row[5], row[6], row[7], row[8])
										                                 
#Caso exista alguma excepcao 
          
		try:
	
# Execute of the variavel "comando"
										                       
			
			c.execute(comando)
			
#Read and save
	
			connection.commit()

#List of the exceptions and list error
			
		except Exception, err:
			print err
			print row
			print
			print 
			
#Close of the connection
	
	connection.close()

	
	pass


csvsql("tp1_part1/csvFile.csv", "nomeBd.db")

distrito = ["Aveiro", "Braga", "Braganca", "VilaReal", "Viana do Castelo", "Porto", 
			 "Guarda", "Castelo Branco", "Viseu", "Coimbra", " Portalegre",
			"Evora", " Setubal", "Lisboa", "Beja", "Faro", "Madeira", "Acores" ]
alunos = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
			
# connetion sqlite of the base dados nomeBd

connection = sqlite3.connect("nomeBd.db")

#for i in distrito:
c = connection.cursor()
c.execute(''' select colocados as total from dados where nomeInstituicao  !="" group by nomeInstituicao order by total DESC LIMIT 30 ''')
# connection cursor of the bd
	
#c = connection.cursor()
	
#Criacao de slects na base dados nomeBd

#c.execute(''' select sum(colocados) as total from dados where nomeInstituicao LIKE '%Aveiro%' group by nomeInstituicao order by total DESC LIMIT 20 ''')
	


	
# save the results
			
connection.commit()
rows = c.fetchall()

# fecha a conexo

connection.close()

	
# criacao do ficheiro outputcsv#


filename = "output.csv1"

spamWriter = csv.writer(open(filename, 'w'), delimiter=',', quotechar='\x22', quoting=csv.QUOTE_MINIMAL)

for row in a:
	
	print row
	
	print
	
	instituicao.append(row[0])
	
	alunos.append(row[1])
	
	spamWriter.writerow([row[0].encode('utf-8')] + [row[1]])


	
plt.xlabel('Instituicao')

plt.ylabel('n. Alunos')

plt.title('Numero de Alunos por Distrito')


x = 0
for distrito in distrito:
	for row in rows:
		if distrito in row['nomeInstituicao']:
			alunos[x] = alunos[x] + row['total']
	x = x + 1


b = lambda:range(1, len(distritos) + 1)

plt.plot(b(), alunos)

locs, labels = plt.xticks(b(), distritos)

plt.setp(labels, 'rotation', 'vertical')

plt.suptitle("ESTATISTICAS", fontsize=20)

plt.grid(True)

plt.show()
